﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using NUnit.Framework;
using SinBiota.Model.Business.MapServices.Implementation;
using SinBiota.Model.Entities;


namespace SinBiota.UnitTests.General
{
    [TestFixture]
    public class ExcelFixture
    {
        [Test]
        public void OpenSpreadsheetTest()
        {
            //var fileName = string.Format("{0}\\fileNameHere", Directory.GetCurrentDirectory());
            const string fileName = "D:\\users\\pedro\\Documents\\scylla\\Biota\\Planilhas\\angiospermae-tests.xls";
            //var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
            var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
            // if xlsx:
            //var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);
            var connection = new OleDbConnection(connectionString);
            connection.Open();

            var sheetList = (from DataRow row in connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows
                             where row["TABLE_NAME"].ToString().EndsWith("$") || row["TABLE_NAME"].ToString().EndsWith("$'")
                             select row["TABLE_NAME"].ToString()).ToList().
                        Select(x => new { val = x, text = x.Replace("'", "").Replace("$", "") });


            var adapter = new OleDbDataAdapter("SELECT * FROM [Planilha1$]", connection);
            var columns = (connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null).Rows.Cast<DataRow>().ToList());
            var a1 = columns.Select(row => new {idx = row[6], name = row[3]}).ToList();

            var a2 = columns.ToString();
            var ds = new DataSet();
            adapter.Fill(ds, "anyNameHere");
            foreach (var row in ds.Tables["anyNameHere"].AsEnumerable())
            {
                var a = row[0].ToString();
            }
        }
    }
}
